package zmee.bid.report;

import com.alibaba.druid.support.logging.Log;
import com.alibaba.druid.support.logging.LogFactory;
import com.kingdee.cosmic.ctrl.common.CtrlUtil;
import kd.bos.algo.*;
import kd.bos.algo.input.CollectionInput;
import kd.bos.dataentity.entity.DynamicObject;
import kd.bos.dataentity.entity.DynamicObjectCollection;
import kd.bos.db.DB;
import kd.bos.db.DBRoute;
import kd.bos.entity.report.AbstractReportListDataPlugin;
import kd.bos.entity.report.FilterInfo;
import kd.bos.entity.report.ReportQueryParam;
import kd.bos.orm.query.QCP;
import kd.bos.orm.query.QFilter;
import kd.bos.servicehelper.BusinessDataServiceHelper;
import zmee.pmas.report.ProExecuteShowQuery;
import zmee.util.DateUtil;

import java.math.BigDecimal;
import java.util.*;

/**
* @author lijiawang
* @Date 2023/1/30 14:52
* @Description:公司项目报表
*/
public class OrgProjectrpttplQuery extends AbstractReportListDataPlugin {
    private static Log log = LogFactory.getLog(OrgProjectrpttplQuery.class);
    @Override
    public DataSet query(ReportQueryParam reportQueryParam, Object o) throws Throwable {
        FilterInfo filterInfo = reportQueryParam.getFilter();
        //组织
        DynamicObjectCollection bosorg = filterInfo.getDynamicObjectCollection("bosorg");
        //日期
        Date yearandmonth = filterInfo.getDate("yearandmonth");
        Calendar calendar=Calendar.getInstance();
        calendar.setTime(yearandmonth);
        int tear = calendar.get(Calendar.YEAR);
        int march = calendar.get(Calendar.MARCH);
        Date begMonth = DateUtil.getBeginDayOfMonth(tear, march);//月份开始时间
        Date endMonth = DateUtil.getEndDayOfMonth(tear, march);//月份结束时间
        Date beginYear = DateUtil.getBeginDayOfYear(tear);//本年开始时间

        //查找期间
        QFilter qFilter=new QFilter("begindate", QCP.large_equals,begMonth)
                .and(new QFilter("enddate",QCP.less_equals,endMonth));
        //查找年份的所有期间
        QFilter qFYear=new QFilter("begindate",QCP.large_equals,beginYear)
                .and(new QFilter("enddate",QCP.less_equals,endMonth));
        //查找的所有期间
        QFilter total= new QFilter("enddate",QCP.less_equals,endMonth);

        DynamicObject bd_period = BusinessDataServiceHelper.loadSingle("bd_period", "id", qFilter.toArray());
        DynamicObject[] period = BusinessDataServiceHelper.load("bd_period", "id", qFYear.toArray());
        DynamicObject[] totals = BusinessDataServiceHelper.load("bd_period", "id", total.toArray());
        String perids="";
        for (int i = 0; i < period.length; i++) {
            if(!"".equals(perids) && perids!=null ){
                perids=perids+",";
            }
            perids=perids+ "'"+period[i].getPkValue().toString()+"'";
        }
        String totalids="";
        for (int i = 0; i < totals.length; i++) {
            if(!"".equals(totalids) && totalids!=null ){
                totalids=totalids+",";
            }
            totalids=totalids+ "'"+totals[i].getPkValue().toString()+"'";
        }
        //项目状态
        DynamicObjectCollection projectstatus = filterInfo.getDynamicObjectCollection("projectstatus");
        //项目分类
        DynamicObjectCollection progroupquery = filterInfo.getDynamicObjectCollection("progroupquery");
        StringBuilder sql=new StringBuilder();
        sql.append("/*dialect*/");
        sql.append("select \n" +
                "pro.fid, pro.fkindid as progroup, pro.fproid as projec,pro.forgid as org, pro.fbillname, pro.fproleaderid as proleader,pro.FPROSTATUS , pmas.fbillno, pmas.ftotalamount as yearbudget, pmas.FYEAR, pmas.fisvalid ,pro.fprojcetbugamt as budgetamt,\n" +
                "sum(if (ter.fperiod='"+bd_period.getPkValue()+"',pay.fthispaymentoftaxamount,0)) as monthpay, " +
                "sum(if (ter.fperiod in ("+totalids+"), pay.fthispaymentoftaxamount,0)) as totalpay,\n" +
                "sum(if (ter.fperiod in ("+perids+"),pay.fthispaymentoftaxamount,0)) as yearpay \n" +
                " from t_pmas_pro_approval pro\n" +
                " left join t_pmas_budget pmas on pro.fproid=pmas.FPROJECTID and pmas.forgid=pro.forgid\n" +
                " left join t_pmct_paymentregister ter on ter.forgid=pro.forgid and ter.fbillstatus='C'\n" +
                " left join t_pmct_paymentregentry pay on pay.fid=ter.fid and pay.fcontproid=pro.fproid \n" +
                " where pro.FBILLSTATUS='C' and pmas.fisvalid = 1");
        sql.append(" and pmas.FYEAR >= DATE_FORMAT('"+tear+"-1-1 00:00:00','%Y-%m-%d %H:%i:%s') and pmas.FYEAR <= DATE_FORMAT('"+tear+"-12-31 23:59:59','%Y-%m-%d %H:%i:%s')");

        if(bosorg != null && bosorg.size() > 0){
            String  orgId="";
            for (int i = 0; i < bosorg.size(); i++) {
                if(!"".equals(orgId) && orgId!=null ){
                    orgId=orgId+",";
                }
                orgId=orgId+ bosorg.get(i).getPkValue().toString();
            }
            sql.append(" and pro.FORGID in("+orgId+") and pmas.FORGID in("+orgId+")");
        }

        if(projectstatus != null && projectstatus.size() > 0){
            String  status="";
            for (int i = 0; i < projectstatus.size(); i++) {
                if(!"".equals(status) && status!=null ){
                    status=status+",";
                }
                status=status+ projectstatus.get(i).getPkValue().toString();
            }
            sql.append(" and pro.FPROSTATUS in("+status+")");
        }

        if(progroupquery != null && progroupquery.size() > 0){
            String  grou="";
            for (int i = 0; i < progroupquery.size(); i++) {
                if(!"".equals(grou) && grou!=null ){
                    grou=grou+",";
                }
                grou=grou+ progroupquery.get(i).getPkValue().toString();
            }
            sql.append(" and pro.fkindid in("+grou+")");
        }

        sql.append(" GROUP BY pro.fproid,pro.forgid order By pro.forgid asc");
        log.info("===SQLQuery===:"+sql.toString());
        DataSet dataSet = DB.queryDataSet(this.getClass().getName(), new DBRoute("scm"), sql.toString(), null);

        DataSet createDataSet = createDs(dataSet, tear, march);

        // 在行末尾添加一行合计 按预算金额 和 本年预算,累计支付,本年支付,本月支付,累计到票金额,本年到票金额,本月到票金额 合计
        DataSet finish = createDataSet.groupBy(null).sum("budgetamt").sum("yearbudget").sum("totalpay").sum("yearpay").sum("monthpay").sum("totalbillamt").sum("yearbillamt").sum("monthbillamt").finish();
        // 将其他字段添加进dataset
        finish = finish.addNullField("progroup", "projec", "org", "proleader");
        // 按照原报表dataset字段，重新排序
        finish = finish.select("org", "projec", "progroup", "proleader", "budgetamt","yearbudget", "totalpay", "yearpay", "monthpay", "totalbillamt", "yearbillamt", "monthbillamt");
        // 将报表dataset和合计行dataset合并
        return createDataSet.union(finish);
    }

    private DataSet createDs(DataSet originDs,int year,int march){
        Collection<Object[]> coll = new ArrayList<>();//创建显示行字段
        //下面初始创建一个空的报表数据行
        RowMeta createRowMeta = getRowMeta();
        Date beginDayOfMonth = DateUtil.getBeginDayOfMonth(year, march);// 获取本月的开始时间
        Date endDayOfMonth = DateUtil.getEndDayOfMonth(year, march);// 获取本月的结束时间
        Date beginDayOfYear = DateUtil.getBeginDayOfYear(year);//本年的开始时间
        Date endDayOfYear = DateUtil.getEndDayOfYear(year);//本年的结束时间

        String[] fields = createRowMeta.getFieldNames();
        for (Row row:originDs.copy()) {
            Object[] obj=new Object[fields.length];
            String org=row.getString("org");
            obj[0] = org;
            String projec=row.getString("projec");
            obj[1] = projec;
            obj[2] = row.getString("progroup");
            obj[3] = row.getString("proleader");
            obj[4] = row.getString("budgetamt");
            obj[5] = row.getString("yearbudget");
            obj[6] = row.getString("totalpay");
            obj[7] = row.getString("yearpay");
            obj[8] = row.getString("monthpay");
            BigDecimal totalbillamt=new BigDecimal("0");
            BigDecimal yearbillamt=new BigDecimal("0");
            BigDecimal monthbillamt=new BigDecimal("0");
            DynamicObject[] ininvoices = BusinessDataServiceHelper.load("pmct_ininvoice", "id,invoicedate,totaloftaxamount", new QFilter[]{
                    new QFilter("org.id", QCP.equals, org)
                            .and(new QFilter("project.id", QCP.equals, projec))
                            .and(new QFilter("billstatus", QCP.equals, "C"))});
            for (DynamicObject inv:ininvoices) {
                Date cedate = inv.getDate("invoicedate");
                if(endDayOfYear.getTime() >= cedate.getTime()){
                    totalbillamt=totalbillamt.add(inv.getBigDecimal("totaloftaxamount"));
                    if(beginDayOfYear.getTime() <= cedate.getTime() && endDayOfYear.getTime() >= cedate.getTime()){
                        yearbillamt=yearbillamt.add(inv.getBigDecimal("totaloftaxamount"));
                    }
                    if(beginDayOfMonth.getTime() <= cedate.getTime() && endDayOfMonth.getTime() >= cedate.getTime()){
                        monthbillamt=monthbillamt.add(inv.getBigDecimal("totaloftaxamount"));
                    }
                }
            }
            obj[9] = totalbillamt;
            obj[10] = yearbillamt;
            obj[11] = monthbillamt;

            coll.add(obj);
        }
        CollectionInput collectionInput = new CollectionInput(createRowMeta, coll);
        return Algo.create(this.getClass().getName()).createDataSet(collectionInput);
    }

    private RowMeta getRowMeta(){
        List<Field> fields = new ArrayList<>();
        fields.add(new Field("org", DataType.StringType));
        fields.add(new Field("projec", DataType.StringType));
        fields.add(new Field("progroup", DataType.StringType));
        fields.add(new Field("proleader", DataType.StringType));
        fields.add(new Field("budgetamt", DataType.BigDecimalType));
        fields.add(new Field("yearbudget", DataType.BigDecimalType));
        fields.add(new Field("totalpay", DataType.BigDecimalType));
        fields.add(new Field("yearpay", DataType.BigDecimalType));
        fields.add(new Field("monthpay", DataType.BigDecimalType));
        fields.add(new Field("totalbillamt", DataType.BigDecimalType));
        fields.add(new Field("yearbillamt", DataType.BigDecimalType));
        fields.add(new Field("monthbillamt", DataType.BigDecimalType));
        return new RowMeta((fields.toArray(new Field[0])));
    }
}
